package export.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.Map.Entry;

import loader.persister.ConnectionManager;
import elements.Constants;
import elements.Deal;
import elements.Parameter;
import elements.Shop;
import elements.User;

public class Loader {
	private Connection conn;
	
	public Loader() {
		conn = new ConnectionManager().getConnection();
	}

	public List<Deal> loadDeals(String createTime, String oid, String startTime, String endTime,
			String discountPercent, String cityName, String category,
			String title, String price, String website, String members,
			int offset, int length, String orderBy, String order) {
		String sql = "select id, website, oid, title, dealUrl, sImageUrl, mImageUrl, lImageUrl, cityName, category, tips, price, dealValue, discountPercent, startTime, endTime, salesNum from deal";
		List<Object> parameters = new ArrayList<Object>();
		
		if (oid != null) {
			sql += " where id = ?";
			parameters.add(oid);
		} else {
			sql += generateSql(parameters, createTime, startTime, endTime, discountPercent, cityName, category, title, price, website, members);
			
			if (orderBy != null) {
				sql += " order by " + orderBy;
			}
			if (order != null && order.equalsIgnoreCase("desc")) {
				sql += " desc";
			}
		
			if (length > 0) {
				sql += " limit ?,?";
				parameters.add(offset);
				parameters.add(length);
			}
		}
		
		//debug information
		//System.out.println("search sql: " + sql);
		//System.out.println("p size: " + parameters.size());
		
		List<Deal> deals = new ArrayList<Deal>();
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			
			for (int i = 0; i < parameters.size(); i++) {
				Object o = parameters.get(i);
				if (o instanceof String) {
					pstmt.setString(i + 1, (String)o);
				}
				else if (o instanceof Double)
					pstmt.setDouble(i + 1, (Double)o);
				else if (o instanceof Long)
					pstmt.setLong(i + 1, (Long)o);
				else if (o instanceof Integer)
					pstmt.setInt(i + 1, (Integer)o);
				else
					System.out.println("other type");
			}
			ResultSet result = pstmt.executeQuery();
			while (result.next()) {
				long dealId = result.getLong("id");
				Deal deal = new Deal();
				deal.setWebsite(result.getString("website"));
				
				//there should not be oid. But as the hihger levels use oid, just set oid = id.
				deal.setOid(Long.toString(dealId));
				deal.setTitle(result.getString("title"));
				deal.setDealUrl(result.getString("dealUrl"));
				deal.setSImageUrl(result.getString("sImageUrl"));
				deal.setMImageUrl(result.getString("mImageUrl"));
				deal.setLImageUrl(result.getString("lImageUrl"));
				deal.setCityName(result.getString("cityName"));
				deal.setCategory(result.getString("category"));
				deal.setTips(result.getString("tips"));
				deal.setPrice(result.getDouble("price"));
				deal.setDealValue(result.getDouble("dealValue"));
				deal.setDiscountPercent(result.getDouble("discountPercent"));
				deal.setStartTime(result.getLong("startTime"));
				deal.setEndTime(result.getLong("endTime"));
				deal.setSalesNum(result.getInt("salesNum"));
				
				String sqlForShop = "select shopName, shopUrl, shopPhone, shopAddress, shopArea, longtitude, latitude from shop where dealId = " + dealId;
				ResultSet shopResult = conn.createStatement().executeQuery(sqlForShop);
				List<Shop> shops = new ArrayList<Shop>();
				while (shopResult.next()) {
					Shop shop = new Shop();
					shop.setShopName(shopResult.getString("shopName"));
					shop.setShopUrl(shopResult.getString("shopUrl"));
					shop.setShopPhone(shopResult.getString("shopPhone"));
					shop.setShopAddress(shopResult.getString("shopAddress"));
					shop.setShopArea(shopResult.getString("shopArea"));
					shop.setLongtitude(shopResult.getDouble("longtitude"));
					shop.setLatitude(shopResult.getDouble("latitude"));
					
					shops.add(shop);
				}
				shopResult.close();
				deal.setShops(shops);
				deals.add(deal);
			}
			result.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return deals;
	}
	
	public User loadUser(String userId) {
		String sql = "select type, website, title, endTime, cityName, price, discountPercent, updateTime, salesNum, distance from user where userId = ?";
		User user = null;
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, userId);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				String type = rs.getString("type");
				String website = rs.getString("website");
				String title = rs.getString("title");
				int endTime = rs.getInt("endTime");
				String cityName = rs.getString("cityName");
				double price = rs.getDouble("price");
				double discountPercent = rs.getDouble("discountPercent");
				long updateTime = rs.getLong("updateTime");
				int salesNum = rs.getInt("salesNum");
				int distance = rs.getInt("distance");
				user = new User(userId, type, website, title, endTime, cityName, price, discountPercent, updateTime, salesNum, distance);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return user;
	}
	
	public void updateUser(String userId, String type, String website,
			String title, String endTime, String cityName, String price,
			String discountPercent, String salesNum, String distance) {
		if (!checkUserExist(userId)) {
			createUser(userId);
		}
		List<Object> paras = new ArrayList<Object>();
		String sqlHead = "update user set";
		String sql = "";
		if (type != null && !type.isEmpty()) {
			sql += ", type = ?";
			paras.add(type);
		}
		if (website != null && !website.isEmpty()) {
			sql += ", website = ?";
			paras.add(website);
		}
		if (title != null && !title.isEmpty()) {
			sql += ", title = ?";
			paras.add(title);
		}
		if (endTime != null && !endTime.isEmpty()) {
			sql += ", endTime = ?";
			paras.add(Integer.parseInt(endTime));
		}
		if (cityName != null && !cityName.isEmpty()) {
			sql += ", cityName = ?";
			paras.add(cityName);
		}
		if (price != null && !price.isEmpty()) {
			sql += ", price = ?";
			paras.add(Double.parseDouble(price));
		}
		if (discountPercent != null && !discountPercent.isEmpty()) {
			sql += ", discountPercent = ?";
			paras.add(Double.parseDouble(discountPercent));
		}
		if (salesNum != null && !salesNum.isEmpty()) {
			sql += ", salesNum = ?";
			paras.add(Integer.parseInt(salesNum));
		}
		if (distance != null & !distance.isEmpty()) {
			sql += ", distance = ?";
			paras.add(Integer.parseInt(distance));
		}
		sql += ", updateTime = " + new Date().getTime();

		sql = sql.substring(1);
		sql = sqlHead + sql + " where userId = ?";
		paras.add(userId);
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			for (int i = 0; i < paras.size(); i++) {
				Object o = paras.get(i);
				if (o instanceof String) {
					pstmt.setString(i + 1, (String)o);
				}
				else if (o instanceof Double)
					pstmt.setDouble(i + 1, (Double)o);
				else if (o instanceof Long)
					pstmt.setLong(i + 1, (Long)o);
				else if (o instanceof Integer)
					pstmt.setInt(i + 1, (Integer)o);
				else
					System.out.println("other type");
			}
			pstmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	private boolean checkUserExist(String userId) {
		String selectSql = "select count(*) from user where userId = ?";
		try {
			PreparedStatement pstmt = conn.prepareStatement(selectSql);
			pstmt.setString(1, userId);
			ResultSet result = pstmt.executeQuery();
			result.next();
			int count = result.getInt(1);
			if (count > 0)
				return true;
			else 
				return false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return true;
	}
	
	private void createUser(String userId) {
		String selectSql = "insert into user(userId, updateTime) values(?,?)";
		try {
			PreparedStatement pstmt = conn.prepareStatement(selectSql);
			pstmt.setString(1, userId);
			pstmt.setLong(2, new Date().getTime());
			pstmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	private String generateSql(List<Object> odlParameters, String createTime, String startTime, String endTime,
			String discountPercent, String cityName, String category,
			String title, String price, String website, String members) {
		String sql = "";
		List<Object> parameters = new ArrayList<Object>();
		if (createTime != null && !createTime.isEmpty()) {
			parameters.add(Long.parseLong(createTime));
			sql += " and createTime > ?";
		}
		if (startTime != null && !startTime.isEmpty()) {
			parameters.add(Long.parseLong(startTime));
			sql += " and startTime > ?";
		}
		if (endTime != null && !endTime.isEmpty()) {
			parameters.add(Long.parseLong(endTime));
			sql += " and endTime > ?";
		}
		if (members != null && !members.isEmpty()) {
			parameters.add(Integer.parseInt(members));
			sql += " and salesNum > ?";
		}
		if (discountPercent != null && !discountPercent.isEmpty()) {
			parameters.add(Double.parseDouble(discountPercent));
			sql += " and discountPercent <= ?";
		}
		if (cityName != null && !cityName.isEmpty()) {
			String[] cityNames = cityName.split(",");
			if (cityNames.length > 0) {
				parameters.add("%" + cityNames[0] + "%");
				sql += " and (cityName like ?";
			}
			for (int i = 1; i < cityNames.length; i++) {
				parameters.add("%" + cityNames[i] + "%");
				sql += " or cityName like ?";
			}
			sql += ")";
		}
		if (website != null && !website.isEmpty()) {
			String[] websites = website.split(",");
			if (websites.length > 0) {
				parameters.add(websites[0]);
				sql += " and (website = ?";
			}
			for (int i = 1; i < websites.length; i++) {
				parameters.add(websites[i]);
				sql += " or website = ?";
			}
			sql += ")";
		}
		if (category != null && !category.isEmpty()){
			parameters.add(category);
			sql += " and category = ?";
		}
		if (title != null && !title.isEmpty()){
			String[] keys = title.split(",");
			if (keys.length > 0) {
				parameters.add("%" + keys[0] + "%");
				sql += " and (title like ?";
			}
			for (int i = 1; i < keys.length; i++) {
				parameters.add("%" + keys[i] + "%");
				sql += " or title like ?";
			}
			sql += ")";
		}
		if (price != null && !price.isEmpty()){
			parameters.add(Double.parseDouble(price));
			sql += " and price < ?";
		}
		odlParameters.addAll(parameters);
		if (parameters.size() == 0)
			return "";
		return " where" + sql.substring(4);
	}
}
